package cn.mothin.frame.core.db;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Component;

import cn.mothin.frame.bean.GridExportBean;
import cn.mothin.frame.core.annotation.Entity;
import cn.mothin.frame.core.exception.SystemException;
import cn.mothin.frame.core.interfaces.ISqlHelper;
import cn.mothin.frame.core.interfaces.vo.IQueryPage;
import cn.mothin.frame.help.JsHelper;
import cn.mothin.frame.util.JsonUtil;
import cn.mothin.frame.util.SqlUtil;
import cn.mothin.frame.util.StringUtil;

@SuppressWarnings("serial")
@Component("51d2f5f4be310e3bec0b5c20")
public class SqlHelper implements ISqlHelper, Serializable {
	protected final Log log = LogFactory.getLog(getClass());
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private JsonUtil jsonUtil;

	private Map<String, List<String>> tableMap = new HashMap<String, List<String>>();

	private synchronized void loadTable(String tableName) {
		if (tableMap.containsKey(tableName)) {
			return;
		}
		List<String> list = new ArrayList<String>();

		Connection connection = null;
		try {
			connection = jdbcTemplate.getDataSource().getConnection();
			ResultSet resultSet = connection.getMetaData().getColumns(null,
					null, tableName, null);
			while (resultSet.next()) {
				list.add(resultSet.getString("COLUMN_NAME"));
			}
			tableMap.put(tableName, list);
		} catch (SQLException e) {
			log.error(e);
			throw new SystemException();
		}
	}

	@Override
	public void execute(String sql, Object... params) {
		try {
			jdbcTemplate.update(sql, params);
		} catch (DataAccessException e) {
			log.error(e);
			throw new SystemException();
		}
	}

	@Override
	public long queryCount(String sql, Object... args) {
		List<Long> list= jdbcTemplate.queryForList(sql,Long.class,args);
		if(list==null|| list.size()==0){
			return 0;
		}
		Long count = list.get(0);
		return count == null ? 0 : count;
	}

	@Override
	public List<Map<String, Object>> queryForList(String sql, Object... args) {
		return jdbcTemplate.queryForList(sql, args);
	}

	@Override
	public void insert(Object object) {
		try {
			Class<?> c = object.getClass();
			if (c.isAnnotationPresent(Entity.class)) {
				Entity entity = c.getAnnotation(Entity.class);
				String tableName = entity.tableName();
				if (tableName == null || tableName.length() == 0) {
					tableName = c.getSimpleName();
				}
				String sql = "insert into " + tableName + "(";
				String valueSql = "values(";
				boolean first = true;
				List<Object> params = new ArrayList<Object>();
				Field[] fields = c.getDeclaredFields();

				for (Field field : fields) {
					if (first) {
						sql += field.getName();
						valueSql += "?";
						first = false;
					} else {
						sql += "," + field.getName();
						valueSql += ",?";
					}
					if (!field.isAccessible()) {
						field.setAccessible(true);
					}
					params.add(field.get(object));
				}
				if (!first) {
					execute(sql + ") " + valueSql + ")", params.toArray());
				}
			}
		} catch (Exception e) {
			log.error(e);
			throw new SystemException();
		}
	}

	@Override
	public void delete(Object object) {
		try {
			Class<?> c = object.getClass();
			if (c.isAnnotationPresent(Entity.class)) {
				Entity entity = c.getAnnotation(Entity.class);
				String tableName = entity.tableName();
				if (tableName == null || tableName.length() == 0) {
					tableName = c.getSimpleName();
				}

				String sql = "DELETE FROM " + tableName + " WHERE ";
				List<Object> params = new ArrayList<Object>();
				boolean b = true;
				for (String pk : entity.pkNames()) {
					Field field = c.getField(pk);
					if (b) {
						sql += pk + "=? ";
						b = false;
					} else {
						sql += "AND " + pk + "=? ";
					}
					if (!field.isAccessible()) {
						field.setAccessible(true);
					}
					params.add(field.get(object));
				}
				execute(sql, params.toArray());
			}
		} catch (Exception e) {
			log.error(e);
		}
	}

	@Override
	public void insertOrUpdate(Object object) {
		try {
			Class<?> c = object.getClass();
			if (c.isAnnotationPresent(Entity.class)) {
				Entity entity = c.getAnnotation(Entity.class);
				String tableName = entity.tableName();
				if (tableName == null || tableName.length() == 0) {
					tableName = c.getSimpleName();
				}
				String sql = "SELECT COUNT(0) FROM " + tableName + " WHERE ";
				List<Object> params = new ArrayList<Object>();
				boolean b = true;
				for (String pk : entity.pkNames()) {
					Field field = c.getField(pk);
					if (b) {
						sql += pk + "=? ";
						b = false;
					} else {
						sql += "AND " + pk + "=? ";
					}
					if (!field.isAccessible()) {
						field.setAccessible(true);
					}
					params.add(field.get(object));
				}
				long l = queryCount(sql, params.toArray());
				if (l > 0) {
					update(object);
				} else {
					insert(object);
				}
			}
		} catch (Exception e) {
			log.error(e);
			throw new SystemException();
		}

	}

	private boolean isPK(String name, String[] pks) {
		for (String s : pks) {
			if (s.equals(name)) {
				return true;
			}
		}
		return false;
	}

	@Override
	public void update(Object object, String... fieldNames) {
		try {
			Class<?> c = object.getClass();
			if (c.isAnnotationPresent(Entity.class)) {
				Entity entity = c.getAnnotation(Entity.class);
				String tableName = entity.tableName();
				if (tableName == null || tableName.length() == 0) {
					tableName = c.getSimpleName();
				}
				String sql = "update " + tableName + " set ";
				boolean first = true;
				List<Object> params = new ArrayList<Object>();

				if (fieldNames.length > 0) {
					for (String s : fieldNames) {
						Field field = c.getField(s);
						if (isPK(field.getName(), entity.pkNames())) {
							continue;
						}
						if (first) {
							sql += s + "=?";
							first = false;
						} else {
							sql += "," + s + "=?";
						}
						if (!field.isAccessible()) {
							field.setAccessible(true);
						}
						params.add(field.get(object));
					}
				} else {
					Field[] fields = c.getDeclaredFields();
					for (Field field : fields) {
						String fieldName = field.getName();
						if (isPK(fieldName, entity.pkNames())) {
							continue;
						}
						if (first) {
							sql += fieldName + "=?";
							first = false;
						} else {
							sql += "," + fieldName + "=?";
						}
						if (!field.isAccessible()) {
							field.setAccessible(true);
						}
						params.add(field.get(object));
					}
				}
				if (!first) {
					sql += " WHERE ";
					boolean b = true;
					for (String pk : entity.pkNames()) {
						Field field = c.getField(pk);
						if (b) {
							sql += pk + "=? ";
							b = false;
						} else {
							sql += "AND " + pk + "=? ";
						}
						if (!field.isAccessible()) {
							field.setAccessible(true);
						}
						params.add(field.get(object));
					}
					execute(sql, params.toArray());
				}
			}
		} catch (Exception e) {
			log.error(e);
			throw new SystemException();
		}
	}

	@Override
	public void insert(String tableName, String json) {
		loadTable(tableName);
		List<String> list = tableMap.get(tableName);

		Map<String, Object> map = jsonUtil.fromJson(json);
		String sql = "insert into " + tableName + "(";
		String valueSql = " values(";
		boolean first = true;
		List<Object> params = new ArrayList<Object>();
		for (String key : map.keySet()) {
			if (list.contains(key)) {
				if (first) {
					sql += key;
					valueSql += "?";
					first = false;
				} else {
					sql += "," + key;
					valueSql += ",?";
				}
				params.add(map.get(key));
			}
		}
		if (!first) {
			valueSql += ")";
			sql += ")" + valueSql;
			execute(sql, params.toArray());
		}
	}

	@Override
	public void update(String tableName, String id, String json) {
		loadTable(tableName);
		List<String> list = tableMap.get(tableName);

		Map<String, Object> map = jsonUtil.fromJson(json);
		String sql = "update " + tableName + " set ";
		boolean first = true;
		List<Object> params = new ArrayList<Object>();
		for (String key : map.keySet()) {
			if (list.contains(key)) {
				if (first) {
					sql += key + "=? ";
					first = false;
				} else {
					sql += "," + key + "=? ";
				}
				params.add(map.get(key));
			}
		}
		if (!first) {
			sql += " where id=?";
			params.add(id);
			execute(sql, params.toArray());
		}
	}

	@Override
	public void delete(String tableName, String id) {
		String sql = "delete from " + tableName + " where id=?";
		execute(sql, id);
	}

	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public <T> List<T> query(Class<T> c, String sql, Object... args) {
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper(c), args);
		// return jdbcTemplate.queryForList(sql, c, args);
	}

	@Override
	public <T> List<T> queryForList(Class<T> c, String sql, Object... args) {
		return jdbcTemplate.queryForList(sql, c, args);
	}

	@Override
	public <T> T queryForObject(Class<T> c, String sql, Object... args) {
		return jdbcTemplate.queryForObject(sql, c, args);
	}

	@Override
	public Map<String, Object> queryForMap(String sql, Object... args) {
		return jdbcTemplate.queryForMap(sql, args);
	}

	@Override
	public String queryForMapJson(String sql, Object... args) {
		return jsonUtil.toJson(queryForMap(sql, args));
	}

	@Override
	public String queryJson(String sql, Object... args) {
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, args);
		return jsonUtil.toJson(list);
	}

	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String queryJson(IQueryPage page, String sql, final Object... values) {
		Map<String, Object> result = new HashMap<String, Object>();
		List<Object> params=new ArrayList<Object>();
		if(values!=null&&values.length>0){
			for(int i=0;i<values.length;i++){
				params.add(values[i]);
			}
		}
		
		sql=SqlUtil.reorganizeSQL(sql,page,params);
		
		String countQueryString = SqlUtil.changeToSelectCount(sql);
		long total = queryCount(countQueryString, params.toArray());
		result.put("total", total);
		// page.setTotal(total.intValue());

		final int maxRow = page.getRows() * page.getPage();
		final int minRow = page.getRows() * (page.getPage() - 1);

		List<Map<String, Object>> list = (List<Map<String, Object>>) jdbcTemplate.query(
				SqlUtil.addOrders(sql, page), params.toArray(), new ResultSetExtractor() {
					@Override
					public List<Map<String, Object>> extractData(
							ResultSet resultSet) throws SQLException,
							DataAccessException {
						List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
						ResultSetMetaData resultSetMetaData = resultSet
								.getMetaData();
						String[] fields = new String[resultSetMetaData
								.getColumnCount()];
						for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
							fields[i - 1] = resultSetMetaData.getColumnLabel(i);
						}
						int currentRow = 0;
						while (resultSet.next() && currentRow < maxRow) {
							if (currentRow >= minRow) {
								Map<String, Object> row = new HashMap<String, Object>();
								for (int i = 0; i < fields.length; i++) {
									row.put(fields[i],
											resultSet.getObject(fields[i]));
								}
								list.add(row);
							}
							currentRow++;
						}
						return list;
					}
				});
		result.put("rows", list);
		return jsonUtil.toJson(result);// page.getPageJsonObject());
	}
	
	/**
	 * 导出下载的EXCEL文件
	 * @param page
	 * @param sql
	 * @param values
	 * @return
	 * 	0：正常
	 * 	1：未提供下载格式
	 * 	2：行数超过65535，拒绝下载
	 */
	@SuppressWarnings({"unchecked","rawtypes"})
	public Map<String, Object> queryAll(IQueryPage page, String sql, final Object... values){
		Map<String, Object> result = new HashMap<String, Object>();
		
		if(StringUtil.isNullOrEmpty(page.getQueryForExportContent())){
			result.put("result", 1);
			return result;
		}
		
		List<Object> params=new ArrayList<Object>();
		if(values!=null&&values.length>0){
			for(int i=0;i<values.length;i++){
				params.add(values[i]);
			}
		}
		
		sql=SqlUtil.reorganizeSQL(sql,page,params);
		
		String countQueryString = SqlUtil.changeToSelectCount(sql);
		long total = queryCount(countQueryString, params.toArray());
		if(total>65535){
			result.put("result", 2);
			return result;
		}
		
		//GridExportBean[] gridExportBeans=jsonUtil.fromJson(page.getQueryForExportContent(),GridExportBean[].class);
		//List<Map<String, Object>> list = jdbcTemplate.queryForList(SqlUtil.addOrders(sql, page), values);
		List<Map<String, Object>> list = (List<Map<String, Object>>) jdbcTemplate.query(
			SqlUtil.addOrders(sql, page), params.toArray(), new ResultSetExtractor() {
				@Override
				public List<Map<String, Object>> extractData(
						ResultSet resultSet) throws SQLException,
						DataAccessException {
					List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
					ResultSetMetaData resultSetMetaData = resultSet
							.getMetaData();
					String[] fields = new String[resultSetMetaData
							.getColumnCount()];
					for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
						fields[i - 1] = resultSetMetaData.getColumnLabel(i);
					}
					while (resultSet.next()) {
						Map<String, Object> row = new HashMap<String, Object>();
						for (int i = 0; i < fields.length; i++) {
							row.put(fields[i],
									resultSet.getObject(fields[i]));
						}
						list.add(row);
					}
					return list;
				}
			}
		);
		result.put("rows", list);
		result.put("result", 0);
		return result;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public byte[] queryAllForExport(IQueryPage page, String sql, final Object... values){
		if(StringUtil.isNullOrEmpty(page.getQueryForExportContent())){
			return null;
		}

		List<Object> params=new ArrayList<Object>();
		if(values!=null&&values.length>0){
			for(int i=0;i<values.length;i++){
				params.add(values[i]);
			}
		}
		
		sql=SqlUtil.reorganizeSQL(sql,page,params);
		
		String countQueryString = SqlUtil.changeToSelectCount(sql);
		long total = queryCount(countQueryString, params.toArray());
		if(total>65535){
			return null;
		}
		
		final GridExportBean[] gridExportBeans=jsonUtil.fromJson(page.getQueryForExportContent(),GridExportBean[].class);
		if(gridExportBeans.length==0){
			return null;
		}

		return (byte[]) jdbcTemplate.query(
			SqlUtil.addOrders(sql, page), params.toArray(), new ResultSetExtractor() {
				@Override
				public byte[] extractData(
						ResultSet resultSet) throws SQLException,
						DataAccessException {
					HSSFWorkbook workbook = new HSSFWorkbook();
					HSSFSheet sheet = workbook.createSheet("export");
					int rowIndex=0;
					// title
					HSSFRow r = sheet.createRow(rowIndex++);
					for(int i=0;i<gridExportBeans.length;i++){
						HSSFCell cell = r.createCell(i);
						cell.setCellValue(gridExportBeans[i].getTitle());
					}
					
					ResultSetMetaData resultSetMetaData = resultSet
							.getMetaData();
					String[] fields = new String[resultSetMetaData
							.getColumnCount()];
					for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
						fields[i - 1] = resultSetMetaData.getColumnLabel(i);
					}
					
					while (resultSet.next()) {
						Map<String, Object> row = new HashMap<String, Object>();
						for (int i = 0; i < fields.length; i++) {
							row.put(fields[i],
									resultSet.getObject(fields[i]));
						}
						r = sheet.createRow(rowIndex++);
						for(int j=0;j<gridExportBeans.length;j++){
							GridExportBean bean=gridExportBeans[j];
							String value=null;
							Object fieldValue=row.get(bean.getField());
							if(StringUtil.isNullOrEmpty(bean.getFormatter())){
								value=fieldValue==null?"":fieldValue.toString();
							}else{
								JsHelper jsHelper=JsHelper.getInstance();
								Object obj=jsHelper.executeFunction(jsHelper.getFunctionName(bean.getFormatter()), fieldValue,row);
								value=obj==null?(fieldValue==null?"":fieldValue.toString()):obj.toString();
							}
							
							HSSFCell cell = r.createCell(j);
							cell.setCellValue(value);
						}
					}
					
					ByteArrayOutputStream baos = new ByteArrayOutputStream();
					try {
						workbook.write(baos);
					} catch (IOException e) {
						e.printStackTrace();
					}
					byte[] bytes = baos.toByteArray();
					try {
						baos.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
					return bytes;
				}
			}
		);
	}

}
